package com.veo.dao.impl;

import com.veo.dao.UserDao;
import com.veo.entity.User;
import com.veo.util.JDBCUtils;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;

import java.util.ArrayList;
import java.util.List;
import java.util.Map;
import java.util.Set;

public class UserDaoImpl implements UserDao {
    private JdbcTemplate template=new JdbcTemplate(JDBCUtils.getDataSource());

    /**
     * 找所有信息
     * @return
     */
    @Override
    public List<User> findAll() {
        //操作数据库
        String sql="select * from user";
        List<User> users = template.query(sql, new BeanPropertyRowMapper<User>(User.class));
        return users;
    }

    /**
     * 登录
     * @param username
     * @param password
     * @return
     */

    @Override
    public User login(String username,String password) {
        try{
            String sql="select * from user where username= ? and password= ?";
            User user=template.queryForObject(sql,new BeanPropertyRowMapper<User>(User.class),username,password);
            return user;
        } catch (Exception e){
            e.printStackTrace();
            return null;
        }
    }

    /**
     * 插入用户
     * @param user
     */
    @Override
    public void addUser(User user) {
        String sql="insert into user values(null,?,?,?,?,?,?,null,null)";
        template.update(sql,user.getName(),user.getGender(),user.getAge(),user.getAddress(),user.getQq(),user.getEmail());
    }

    /**
     * 删除用户
     * @param id
     */
    @Override
    public void deleteUser(String id) {
        String sql="delete from user where id= ?";
        template.update(sql,id);
    }

    /**
     * id找用户
     * @param id
     * @return
     */
    @Override
    public User findUserById(int id) {
        String sql="select * from user where id= ?";
        User user = template.queryForObject(sql, new BeanPropertyRowMapper<User>(User.class), id);
        return user;
    }

    /**
     * 获取总记录数
     * @return
     * @param condition
     */
    @Override
    public int findTotalCount(Map<String, String[]> condition) {
        //定义模板初始化sql
        String sql="select count(*) from user where 1 = 1 ";
        StringBuffer sb=new StringBuffer(sql);
        //遍历map
        Set<String> KeySet = condition.keySet();
        //获取参数的集合
        List<Object> params=new ArrayList<Object>();
        for (String key : KeySet) {
            //排除分页的条件
            if("currentPage".equals(key) || "rows".equals(key)){
                continue;
            }
            String value = condition.get(key)[0];
            //判断value是否有值
            if(value!=null && !"".equals(value)){
                sb.append(" and "+key+" like ? ");
                params.add("%"+value+"%");//加问号？条件的值
            }
        }
//        System.out.println(sb.toString());
//        System.out.println(params);
        //sql需要装换成字符串,参数要装换成数组
        return template.queryForObject(sb.toString(), Integer.class,params.toArray());
    }

    /**
     * 获取每一页的记录数(查询条件)
     * @param start
     * @param rows
     * @param condition
     * @return
     */
    @Override
    public List<User> findByPage(int start, int rows, Map<String, String[]> condition) {
        String sql="select * from user where 1 = 1 ";

        StringBuffer sb=new StringBuffer(sql);
        //遍历map
        Set<String> KeySet = condition.keySet();
        //获取参数的集合
        List<Object> params=new ArrayList<Object>();
        for (String key : KeySet) {
            //排除分页的条件
            if("currentPage".equals(key) || "rows".equals(key)){
                continue;
            }
            String value = condition.get(key)[0];
            //判断value是否有值
            if(value!=null && !"".equals(value)){
                sb.append(" and "+key+" like ? ");
                params.add("%"+value+"%");//加问号？条件的值
            }
        }
        //添加分页查询
        sb.append("limit ? , ? ");
        //添加分页数据
        params.add(start);
        params.add(rows);
//        System.out.println(sb.toString());
//        System.out.println(params);
        //sql需要装换成字符串,参数要装换成数组
        return template.query(sb.toString(),new BeanPropertyRowMapper<User>(User.class),params.toArray());
    }

    /**
     * 修改用户信息
     * @param user
     */
    public void updateUser(User user) {
        String sql="update user set name= ? ,gender= ? ,age= ? ,address= ? ,qq= ? ,email= ? where id= ?";
        template.update(sql,user.getName(),user.getGender(),user.getAge(),user.getAddress(),user.getQq(),user.getEmail(),user.getId());
    }
}
